package com.pension.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.pension.model.dto.Room;

public class RoomDao {

	public Room getRoomByName(String name) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Room room = null;

		try {
			conn = ConnectionHelper.getConnection();
			// 3. 명령 생성
			String sql = "SELECT roomname, roomnumber, roomtype, maxpeople, acreagesize, priceroom "
					+ "FROM rooms WHERE roomname = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			// 4. 명령 실행
			rs = pstmt.executeQuery();
			// 5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				room = new Room();

				room.setRoomNumber(rs.getInt(2));
				room.setRoomName(rs.getString(1));
				room.setRoomType(rs.getString(3));
				room.setMaxPeople(rs.getInt(4));
				room.setAcreageSize(rs.getInt(5));
				room.setPriceRoom(rs.getInt(6));
				
			}

		} catch (Exception ex) {

		} finally {
			try {
				rs.close();
			} catch (Exception ex) {
			}
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}

		return room;
	}

	public List<Room> getRooms() {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Room> roomslist = new ArrayList<Room>();

		try {
			conn = ConnectionHelper.getConnection();
			String sql = "SELECT roomnumber, roomname, roomtype, maxpeople, acreagesize, priceroom "
					+ "FROM rooms";
			pstmt = conn.prepareStatement(sql);
			// 4. 명령 실행
			rs = pstmt.executeQuery();
			// 5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Room rooms = new Room();

				rooms.setRoomNumber(rs.getInt(1));
				rooms.setRoomName(rs.getString(2));
				rooms.setRoomType(rs.getString(3));
				rooms.setMaxPeople(rs.getInt(4));
				rooms.setAcreageSize(rs.getInt(5));
				rooms.setPriceRoom(rs.getInt(6));

				roomslist.add(rooms);
			}

		} catch (Exception ex) {

		} finally {
			try {
				rs.close();
			} catch (Exception ex) {
			}
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}

		return roomslist;
	}

	public int getRoomsCount() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int resc = 0;

		try {
			conn = ConnectionHelper.getConnection();
			String sql = "select count(*) from pension.rooms";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				
				resc = rs.getInt(1);
			}

		} catch (Exception ex) {

		} finally {
			try {
				rs.close();
			} catch (Exception ex) {
			}
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}
		
		return resc;
	}

	public void insertRoom(Room room)
	{
		
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			
			conn = ConnectionHelper.getConnection();

			// 3. 명령 생성
			String sql = "INSERT INTO rooms "
					+ "(roomnumber, roomname, roomtype, maxpeople, acreagesize, priceroom) "
					+ "VALUES (?, ?, ?, ?, ?, ?);";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, room.getRoomNumber());
			pstmt.setString(2, room.getRoomName());
			pstmt.setString(3, room.getRoomType());
			pstmt.setInt(4, room.getMaxPeople());
			pstmt.setInt(5, room.getAcreageSize());
			pstmt.setInt(6, room.getPriceRoom());
			// 4. 명령 실행
			pstmt.executeUpdate();
		} catch (Exception ex) {

		} finally {
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}
		
	}

	public void updateRoom(Room room) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE rooms " +
				"SET roomname = ?, roomtype = ?, maxpeople = ?, acreagesize = ?, priceroom = ? " + 
				"WHERE roomnumber = ?";
			pstmt = conn.prepareStatement(sql);
			
			
			pstmt.setString(1, room.getRoomName());
			pstmt.setString(2, room.getRoomType());
			pstmt.setInt(3, room.getMaxPeople());
			pstmt.setInt(4, room.getAcreageSize());
			pstmt.setInt(5, room.getPriceRoom());
			pstmt.setInt(6, room.getRoomNumber());
			pstmt.executeUpdate();
			
		} catch (Exception ex) {

			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}

		
		
		
		
	}

	public Room getRoomByRoomNumber(int parseInt) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Room room = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuffer sql = new StringBuffer(300);
			sql.append("SELECT "); 
			sql.append("roomnumber, roomname, roomtype, maxpeople, acreagesize, priceroom ");
			sql.append("FROM rooms ");
			sql.append("WHERE roomnumber = ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, parseInt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				room = new Room();
				
				room.setRoomNumber(rs.getInt(1));
				room.setRoomName(rs.getString(2));
				room.setRoomType(rs.getString(3));
				room.setMaxPeople(rs.getInt(4));
				room.setAcreageSize(rs.getInt(5));
				room.setPriceRoom(rs.getInt(6));
				
			}			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception ex) { }
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return room;
	}

	public void deleteRoom(int parseInt) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"DELETE FROM rooms WHERE roomnumber = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, parseInt);
			
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
	}

}
